package com.xj.dao;

import com.xj.entity.InvUpdateInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface InventoryDao extends JpaRepository<InvUpdateInfo, Integer> {

      @Query(value = "SELECT  cmaker as cmaker,cverifier as cverifier,cast(id as  varchar) as id,convert(VARCHAR,dcreatesystime,120) AS createtime FROM  SO_SOMain WHERE cSOCode=:saleordercode",nativeQuery = true)
      List<Map<String,String>>    checkSaleorder(String  saleordercode);

      @Modifying
      @Transactional
      @Query(value = "delete  from  SO_SODetails  where id=:id",nativeQuery = true)
      int  deleteOrderDetailByid(String id);

    @Modifying
    @Transactional
    @Query(value = "delete  from  SO_SOMain  where id=:id",nativeQuery = true)
    int  deleteOrderMainByid(String id);


    @Modifying
    @Transactional
    @Query(value = "delete  from  DispatchLists  where dlid=:dlid",nativeQuery = true)
    int  deleteDispatchListsBydlid(String dlid);

    @Modifying
    @Transactional
    @Query(value = "delete  from  DispatchList  where dlid=:dlid",nativeQuery = true)
    int  deleteDispatchListBydlid(String dlid);

      @Query(value = "SELECT cmaker   as cmaker,cverifier as cverifier, cast(dlid  as varchar) as dlid,convert(VARCHAR,dcreatesystime,120) as  createtime FROM  DispatchList  WHERE cDLCode=:saleorder",nativeQuery = true)
      List<Map<String,String>>   checkDispatch(String saleorder);


    @Query(value = "SELECT   cinvcode,isnull(iquantity,0)  iquantity,irowno  FROM  DispatchLists  where dlid=:dlid",nativeQuery = true)
    List<Map<String,Object>>  getDetailBymainid(String dlid);

    @Modifying
    @Transactional
    @Query(value = "update  DispatchList set  cshipaddress=:#{#dto.cshipaddress},ccusperson=:#{#dto.ccusperson},cdefine11=:#{#dto.cdefine11}," +
            "ccontactname=:#{#dto.ccontactname},cmobilephone=:#{#dto.cmobilephone},cmemo=:#{#dto.cmemo},dcreatesystime=:#{#dto.createtime}  where dlid=:#{#dto.dlid}",nativeQuery = true)
    int  updateDispatch(DispatchListDto dto);


    @Modifying
    @Transactional
    @Query(value = "update  DispatchLists set cinvcode=:#{#dto.cinvcode},cinvname=:#{#dto.cinvname},iquotedprice=:#{#dto.iquotedprice},  " +
            "iunitprice=:#{#dto.iunitprice},itaxunitprice=:#{#dto.itaxunitprice}, imoney=:#{#dto.imoney},itax=:#{#dto.itax},isum=:#{#dto.isum}," +
            "inatunitprice=:#{#dto.inatunitprice}, inatmoney=:#{#dto.inatmoney},inattax=:#{#dto.inattax},inatsum=:#{#dto.inatsum},cmemo=:#{#dto.cmemo},kl=:#{#dto.kl}  " +
            " where irowno=:#{#dto.irowno} and dlid=:#{#dto.dlid} ",nativeQuery = true)
    int  updateDispatchs(DispatchlistsDto dto);


     List<InvUpdateInfo> getInvUpdateInfoByCategory(String category);

     @Modifying
     @Transactional
     @Query(value = "DELETE  FROM  xj_inv WHERE category=:category and createtime<:createDate  ",nativeQuery = true)
     int  deleteAllByCategory(String  category, Date createDate);


    @Query(value = "select  cinvcode  from  inventory  where cinvccode>=:begincategorycode  and  cinvccode<=:endcategorycode  ",nativeQuery = true)
    List<String>  getB2Binvcode(String begincategorycode,String  endcategorycode);

    @Query(value = "SELECT   inv.cinvcode as invcode, isnull(iquantity,0)+isnull(finquantity,0)-isnull(foutquantity,0) AS availqty   FROM   Inventory  inv " +
            " LEFT JOIN  sa_CurrentStock c ON  inv.cInvCode=c.cInvCode   WHERE  inv.cInvCCode=:category   AND  c.cWhCode='bzp' ",nativeQuery = true)
    List<Map<String,Object>> getCurrentStock(String  category);

    @Query(value = "SELECT autoid as autoid   from  currentStock  WHERE cWhCode=:cwhcode AND  cinvcode=:cinvcode",nativeQuery = true)
     List<Map<String,Integer>>  getCurrentStockBycinvcode(String cwhcode,String cinvcode);

    @Query(value = "select max(itemid) from currentstock",nativeQuery = true)
     Integer  getItemid();

    @Transactional
    @Modifying
    @Query(value = "insert CurrentStock(cWhCode, cInvCode,ItemId, cBatch, cVMIVenCode,iSoType, iSodid, iQuantity, iNum, " +
            "cFree1, cFree2, fOutQuantity, fOutNum, fInQuantity, fInNum, cFree3, cFree4, cFree5, cFree6,cFree7, cFree8,cFree9, " +
            "cFree10, dVDate, bStopFlag, fTransInQuantity, dMdate, fTransInNum, fTransOutQuantity, fTransOutNum, fPlanQuantity, fPlanNum," +
            "fDisableQuantity, fDisableNum, fAvaQuantity, fAvaNum,iMassDate,BGSPSTOP, cMassUnit,fStopQuantity,fStopNum, dLastCheckDate," +
            "cCheckState, dLastYearCheckDate, iExpiratDateCalcu, cExpirationdate, dExpirationdate, ipeqty,ipenum) " +
            "VALUES ( 'bzp', :cinvocde, :itemid, '', '', 0, '', 0, 0, '', '',:foutquantity, 0, 0, 0, '', '', '', '', '', '', '', '', NULL, 0,0, NULL, 0, 0, 0, 0, 0, \n" +
            "0, 0, 0, 0,NULL, 0, 0, 0, 0, NULL, '', NULL, 0, NULL, NULL, 0, 0)",nativeQuery = true)
    int insertCurrentstock(String cinvocde,BigDecimal foutquantity,Integer itemid);
    @Modifying
    @Transactional
    @Query(value = "update currentStock set foutquantity=foutquantity+:iquantity   where autoid=:autoid",nativeQuery = true)
    int  updateCurrentStock(Integer autoid,BigDecimal iquantity);

    @Query(value = "SELECT * FROM   SA_InvPriceJustMain WHERE ccode=:vouchercode ",nativeQuery = true)
   Map<String,Object>  getPriceJustMainInfoBycode(String vouchercode);


    @Query(value = "SELECT   u.cComUnitName as unitname , d.* FROM   SA_InvPriceJustDetail d  INNER JOIN Inventory inv  " +
            "ON  d.cinvcode=Inv.cInvCode INNER JOIN  ComputationUnit  u  ON  inv.cComUnitCode=u.ccomunitcode  WHERE d.id=:id ",nativeQuery = true)
    List<Map<String,Object>>  getPriceJustDetailInfoBymainid(String id);


}


